<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  

  
  <title>MySQL数据库操作（二）——DQL | 海晨忆的博客</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  
  
  <meta name="description" content="DQL关键字：select、from、where、group by、having、roder by">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL数据库操作（二）——DQL">
<meta property="og:url" content="https://haichenyi.com/2018/10/11/MySQL数据库操作（二）——DQL/index.html">
<meta property="og:site_name" content="海晨忆的博客">
<meta property="og:description" content="DQL关键字：select、from、where、group by、having、roder by">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2018-10-11T15:15:53.445Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="MySQL数据库操作（二）——DQL">
<meta name="twitter:description" content="DQL关键字：select、from、where、group by、having、roder by">
  
    <link rel="alternate" href="/atom.xml" title="海晨忆的博客" type="application/atom+xml">
  
  
    <link rel="icon" href="/uploads/artistic_image/head.jpg">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/css/style.css">
  <link rel="stylesheet" href="/css/highlight.css">
  <script>
  let antiquityStorage = window.sessionStorage.getItem('antiquitySessionStorage');
  if (antiquityStorage == '' || antiquityStorage == null) {
    var antiquityLoader = '<div id="loaderbox"><div class="loader"><div class="load-roll"><div class="load-top"></div><div class="load-right"></div><div class="load-bottom"></div></div></div></div>';
    document.write(antiquityLoader);
    document.body.style.overflow = 'hidden'
  }
  </script>
</head>

<body>
  <div id="fullpage" class="mobile-nav-right">
    
      <div id="wrapper" style="background-image: url(/uploads/artistic_image/bg.jpg)" title="背景图片来自网络">
    
    
      <header id="header">
  <div id="nav-toggle" class="nav-toggle"></div>
  <div class="head-box global-width">
    <nav class="nav-box nav-right">
      
        <a class="nav-item" href="/" title
        
        >首页</a>
      
        <a class="nav-item" href="/archives" title
        
        >归档</a>
      
        <a class="nav-item" href="/about" title
        
        >简历</a>
      
    </nav>
  </div>
</header>
      <div id="middlecontent" title class="global-width sidebar-left">
        <section id="main"><article id="post-MySQL数据库操作（二）——DQL" class="article global-container article-type-post" itemscope itemprop="blogPost">
  
    <header class="article-header">
      
  
    <h1 class="article-title" itemprop="name">
      MySQL数据库操作（二）——DQL
    </h1>
  

    </header>
  
  <div class="article-meta">
    <a href="/2018/10/11/MySQL数据库操作（二）——DQL/" class="article-date">
  <time datetime="2018-10-11T14:49:56.000Z" itemprop="datePublished">2018-10-11</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a>
  </div>

    
  </div>
  
    <span id="busuanzi_container_page_pv">
      本文总阅读量<span id="busuanzi_value_page_pv"></span>次
    </span>
  

  <div class="article-inner">
    
    <div class="article-content article-content-cloud doorframe mac" itemprop="articleBody">
      
        <h3 id="DQL"><a href="#DQL" class="headerlink" title="DQL"></a>DQL</h3><p>关键字：select、from、where、group by、having、roder by</p>
<a id="more"></a>
<h4 id="基本查询"><a href="#基本查询" class="headerlink" title="基本查询"></a>基本查询</h4><p>&emsp;&emsp;<strong>查询所有：</strong><code>select * from 表名;</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select * from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>查询部分列</strong><code>select 列名,...列名 from 表名;</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select stu_num,stu_name from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>查询去除完全重复的列</strong><code>select distinct * from 表名;</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">select distinct * from t_stu;</span><br><span class="line">select distinct stu_age from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>也可以查询同时做加、减、乘、除运算操作</strong>：<br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">//把查询出来的年龄都乘以2倍。</span><br><span class="line">select stu_age*2 from t_stu;</span><br><span class="line">//如果查出来的年龄为null，就设置为29</span><br><span class="line">select ifnull(stu_age,29) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>做连接字符串操作：CONCAT</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">//把名字和年龄拼接起来</span><br><span class="line">select CONCAT(stu_name,stu_age) from t_stu;</span><br><span class="line"></span><br><span class="line">select CONCAT(&apos;我的名字是：&apos;,stu_name,&apos;，我今年&apos;,stu_age,&apos;岁&apos;) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>给列起别名：as</strong><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select stu_age as 年龄 from t_stu;</span><br><span class="line">select stu_age as 年龄,stu_name as 姓名 from t_stu;</span><br><span class="line">select CONCAT(stu_name,stu_age) as 描述 from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<h4 id="条件查询"><a href="#条件查询" class="headerlink" title="条件查询"></a>条件查询</h4><p>&emsp;&emsp;跟前面一篇讲的更新，删除里面设置条件的方法是一样的。<code>where</code>后面跟条件<br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">//查询年龄大于等于20的学生</span><br><span class="line">select * from t_stu where stu_age&gt;=20;</span><br><span class="line">//查询年龄在15到25岁之间的学生</span><br><span class="line">select * from t_stu where stu_age between 15 and 25;</span><br><span class="line">//查询名字叫zhangsan,lisi,wangwu.zhaoliu的学生</span><br><span class="line">select * from t_stu where stu_name in(&apos;zhangsan&apos;,&apos;lisi&apos;,&apos;wangwu&apos;,&apos;zhangliu&apos;);</span><br></pre></td></tr></table></figure></div></p>
<p>&emsp;&emsp;<strong>模糊查询</strong><code>like</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">//一个字加一个下划线，两个字就是两个下划线</span><br><span class="line">//查询名字中张开头，并且是两个字的学生.</span><br><span class="line">select * from t_stu where stu_name like &apos;张_&apos;;</span><br><span class="line">//查询名字是三个字的学生</span><br><span class="line">select * from t_stu where stu_name like &apos;___&apos;;</span><br><span class="line"></span><br><span class="line">//百分号%匹配0~N个字符</span><br><span class="line">//查询名字中以雷结尾的学生</span><br><span class="line">select * from t_stu where stu_name like &apos;%雷&apos;;</span><br><span class="line">//查询名字中包含晓的学生</span><br><span class="line">select * from t_stu where stu_name like &apos;%晓%&apos;;</span><br></pre></td></tr></table></figure></div></p>
<h4 id="排序-order-by"><a href="#排序-order-by" class="headerlink" title="排序 order by"></a>排序 <code>order by</code></h4><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">// desc:降序，asc：升序</span><br><span class="line">//按学生年龄升序排列</span><br><span class="line">select * from t_stu ORDER BY stu_age ASC;</span><br><span class="line">//按学生年龄降序排列</span><br><span class="line">select * from t_stu ORDER BY stu_age DESC;</span><br><span class="line">//年龄相同的时候，按名字降序排列。可以无限添加排序条件</span><br><span class="line">select * from t_stu ORDER BY stu_age ASC,stu_name DESC;</span><br></pre></td></tr></table></figure></div>
<h4 id="聚合函数-纵向查询"><a href="#聚合函数-纵向查询" class="headerlink" title="聚合函数(纵向查询)"></a>聚合函数(纵向查询)</h4><p><code>计数count</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">//只要不为null，就+1</span><br><span class="line">select count(*) from t_stu;</span><br><span class="line">select count(stu_age) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p><code>计算和sum</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">//计算学生年龄加起来的总数</span><br><span class="line">select sum(stu_age) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p><code>最大值max,最小值min</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">//查询年龄中最大的</span><br><span class="line">select max(stu_age) from t_stu;</span><br><span class="line">//查询年龄中最小的</span><br><span class="line">select min(stu_age) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<p><code>平均值avg</code><br><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select avg(stu_age) from t_stu;</span><br></pre></td></tr></table></figure></div></p>
<h4 id="分组查询group-by"><a href="#分组查询group-by" class="headerlink" title="分组查询group by"></a>分组查询<code>group by</code></h4><p>写法：<code>select 条件,聚合函数,...,聚合函数 from 表名 group by 条件;</code></p>
<p>分组查询必须都是聚合函数，并且，上面两个位置的条件必须相同</p>
<div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">//按老师分组查询，每组老师名下的学生个数</span><br><span class="line">select stu_teacher,count(*) from t_stu group by stu_teacher;</span><br><span class="line"></span><br><span class="line">//分组前条件，不满足条件的没有参加分组</span><br><span class="line">//按老师分组查询，查询每组老师名下年龄大于20岁的学生的个数</span><br><span class="line">select stu_teacher,count(*) from t_stu where stu_age&gt;20 group by stu_teacher;</span><br><span class="line"></span><br><span class="line">//having 分组后条件</span><br><span class="line">//按老师分组查询，查询老师名下年龄大于20岁的学生，并且剔除学生个数小于5个的老师</span><br><span class="line">select stu_teacher,count(*) from t_stu where stu_age&gt;20 group by stu_teacher having count(*)&lt;5;</span><br></pre></td></tr></table></figure></div>
<h4 id="limit-MySQL特有的"><a href="#limit-MySQL特有的" class="headerlink" title="limit(MySQL特有的)"></a>limit(MySQL特有的)</h4><div class="highlight-box"autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false" contenteditable="true"data-rel="PLAIN"><figure class="iseeu highlight /plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">//从下标0开始，往后查询5条数据</span><br><span class="line">select * from t_stu limit 0,5;</span><br><span class="line"></span><br><span class="line">//分页查询，比方说如果你要查第N页的数据，每页数据M条</span><br><span class="line">//(当前页-1)*每页的数据数</span><br><span class="line">select * from t_stu limit (N-1)*M,M;</span><br></pre></td></tr></table></figure></div>
      
    </div>
    
      <footer class="article-footer">
        完
      </footer>
    
  </div>
  
    
<nav id="article-nav">
  <div class="article-nav-block">
    
      <a href="/2018/10/11/MySQL数据库操作（三）——数据库的备份与恢复/" id="article-nav-newer" class="article-nav-link-wrap">
        <strong class="article-nav-caption"></strong>
        <div class="article-nav-title">
          
            MySQL数据库操作（三）——数据库的备份与恢复
          
        </div>
      </a>
    
  </div>
  <div class="article-nav-block">
    
      <a href="/2018/10/11/MySQL数据库操作（一）——DDL、DML、DCL/" id="article-nav-older" class="article-nav-link-wrap">
        <div class="article-nav-title">MySQL数据库操作（一）——DDL、DML、DCL</div>
        <strong class="article-nav-caption"></strong>
      </a>
    
  </div>
</nav>

    
  
  
</article>
</section>
        <aside id="sidebar">
  
    <div class="widget-box">
  <div class="avatar-box avatar-item">
    <img class="avatar" src="/uploads/artistic_image/head.jpg" title="头像来自网络"></img>
    <h3 class="avatar-name">
      
        海晨忆
      
    </h3>
    <p class="avatar-slogan">
      先谋生，再谋爱。人间值得，未来可期。
    </p>
  </div>
</div>


  
    
  <div class="widget-box">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/Android-Socket/">Android -Socket</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-加密算法/">Android -加密算法</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-常用功能/">Android -常用功能</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-框架/">Android -框架</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-源码解析/">Android -源码解析</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-自定义view/">Android -自定义view</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Android-设计模式/">Android -设计模式</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/JAVA-并发/">JAVA -并发</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Java-SpringBoot/">Java -SpringBoot</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/Tomcat/">Tomcat</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/kotlin/">kotlin</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-JDBC/">数据库 -JDBC</a></li><li class="category-list-item"><a class="category-list-link" href="/categories/数据库-MySQL/">数据库 -MySQL</a></li></ul>
    </div>
  </div>


  
    

  
    
  
    
  <div class="widget-box">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2021/07/">July 2021</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/11/">November 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/10/">October 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/09/">September 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/08/">August 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/07/">July 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2019/06/">June 2019</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/10/">October 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/05/">May 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">March 2018</a></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/02/">February 2018</a></li></ul>
    </div>
  </div>

  
    
  <div class="widget-box">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/2021/07/27/JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁/">JAVA—公平锁，非公平锁，悲观锁，乐观锁，死锁</a>
          </li>
        
          <li>
            <a href="/2021/07/19/JAVA—线程同步器AQS/">JAVA—线程同步器AQS</a>
          </li>
        
          <li>
            <a href="/2021/07/07/Android模拟点击/">Android模拟点击</a>
          </li>
        
          <li>
            <a href="/2019/11/27/SpringBoot系列-消息-RabbitMQ-（二十）/">SpringBoot系列-消息(RabbitMQ)（二十）</a>
          </li>
        
          <li>
            <a href="/2019/11/13/SpringBoot系列-Docker（十九）/">SpringBoot系列-Docker（十九）</a>
          </li>
        
      </ul>
    </div>
  </div>

  
      <div class="widget-box">
    <h3 class="widget-title">友情链接</h3>
    <div class="widget">
      
        <a class="hrf" style="display: block;" href="https://github.com/haichenyi" title target='_blank'
        >Github</a>
      
        <a class="hrf" style="display: block;" href="https://www.jianshu.com/u/6077ee440c37" title target='_blank'
        >简书</a>
      
        <a class="hrf" style="display: block;" href="https://blog.csdn.net/qq_27634797" title target='_blank'
        >CSDN</a>
      
    </div>
  </div>

  
</aside>
      </div>
      <footer id="footer">
  <div class="foot-box footers global-width">
    &copy;2017-2021 海晨忆 &nbsp;&nbsp;
    <script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span id="busuanzi_container_site_pv">阁下是第<span id="busuanzi_value_site_pv"></span>个访客</span>
  </div>
</footer>
      <script src="https://code.jquery.com/jquery-2.0.3.min.js"></script>
<script>
if (!window.jQuery) {
var script = document.createElement('script');
script.src = "/js/jquery-2.0.3.min.js";
document.body.write(script);
}
</script>

  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/script.js"></script>



    </div>
    <nav id="mobile-nav" class="mobile-nav-box">
  <div class="mobile-nav-img mobile-nav-top"></div>
  
    <a href="/" class="mobile-nav-link">首页</a>
  
    <a href="/archives" class="mobile-nav-link">归档</a>
  
    <a href="/about" class="mobile-nav-link">简历</a>
  
  <div class="mobile-nav-img  mobile-nav-bottom"></div>
</nav>    
  </div>
</body>
</html>